﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using ContractManagementSystem.BusinessLogicLayer;

namespace ContractManagementSystem.DataAccessLayer
{
    public static class CommonDao
    {
        // To create a connection to SQL Server
        public static SqlConnection CreateConnection()
        {
            // To get connection string from file web.config
            var connObject = ConfigurationManager.ConnectionStrings["DataConnectionString"];

            if (connObject != null)
            {
                var connString = connObject.ToString();
                return new SqlConnection(connString); // Return a sql connection
            }

            // Return default 
            return null;
        }

        // To get datatable on a connection created before
        public static DataTable GetDataTable(string sql)
        {
            var connection = CreateConnection(); // Init a variable connection

            try
            {
                var sqlCommand = new SqlCommand(sql, connection);
                connection.Open();

                // Declare a variable SqlDataReader
                var dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                using (var dt = new DataTable())
                {
                    // Create a new CultureInfo for the United Kingdom.
                    dt.Locale = new CultureInfo("en-gb");

                    if (dataReader != null) dt.Load(dataReader);
                    sqlCommand.Dispose();
                    return dt;
                }
            }
            catch (SqlException)
            {
                return null;
            }
            finally
            {
                connection.Close();
            }
        }

        // Excute a Scalar sql
        public static string ExecuteSqlScalar(string sql)
        {
            var connection = CreateConnection(); // Init a connection

            try
            {
                connection.Open();
                var cmd = connection.CreateCommand();
                cmd.CommandText = sql;

                var result = CommonLogic.ConvertToString(cmd.ExecuteScalar());

                return result;
            }
            catch (SqlException)
            {
                return null;
            }
            finally
            {
                connection.Close();
            }
        }

        // Execute a sql command
        public static string ExecuteSqlCommand(string sql)
        {
            var connection = CreateConnection(); // Init a connection

            try
            {
                var sqlCommand = new SqlCommand(sql, connection);
                connection.Open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Dispose();
                return "Successfully!"; // Operation to database is ok.
            }
            catch (SqlException se)
            {
                return "So sorry, unsuccessfully! " + se.Message;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
